In Online Analytical Processing (OLAP) systems, huge amounts of data can be evaluated with basically three operations: consolidation, drill-down, roll-up and “slicing and dicing.” Consolidation means that aggregates from the data to be evaluated are computed in one or more dimensions. The drill-down is a technique that allows users to navigate through the details. It describes the dimensions the data is evaluated in by the user. The last operation is the slice and dice operation. This describes a feature where users can take out (slicing) a specific set of data of the cube and view (dicing) the slices from a different viewpoint.
In one particular OLAP implementation, inside the SAP Business Warehouse (BW) product, the slice and dice operation is a standard operation. Here, the slicing is expressed via processes known as selections. Taking a closer look there are different types of selections happening in SAP BW.
The basic selection can be represented in standard relational algebra. A selection defines a comparator between a characteristic, also called a dimension or attribute, and a constant value (i.e., a characteristic value). All result tuples must apply to this comparator, e.g. “Business Partner=‘Bill’. This selection affects the aggregates, and the aggregated values consist only of key figure values of tuples that apply to the filters. These key figure values are also known as measures. This basic selection is also called a data filter, as it filters the data on database layer before any aggregations happen.
Besides the data filters, so-called visual filters exist. Visual filters are defined similarly to a characteristic data filter. A selection such as “Business Partner=‘Bill’ as part of a visual filter would result in a result set that also only contains tuples where the business partner is “Bill”. The difference lies in the aggregation behavior. As a data filter is executed on the data base layer, it affects the totals. A visual filter is executed after any aggregations, and therefore it just omits tuples in the display of the result set. Aggregates are created without considering the visual filter.
In some OLAP systems such as SAP's BW there are is a third type of filter. This type of filter is different from the other two in that the comparator is not defined between a characteristic and a constant, but rather is defined between a key figure and a constant value. So the comparator in this third type of filter could be represented such as “Revenue>‘10.000’. This feature is called a “condition” in SAP BW. A condition is always defined with respect to one or more dimensions. Conditions act like a visual filter. So they do not affect totals that are more coarse than the level at which the condition is defined.
Putting these filters into a matrix of data filters and visual filters against characteristic filters and key figure filters the table in FIG. 8 is drawn. What is missing is a key figure data filter, which is needed to complete the provided feature set of an analytical engine some OLAP systems, such as SAP BW.
To understand how any selection can influence the OLAP result set, it must first be clear how the OLAP result set looks. Basically, the OLAP result set is form of a pivot table, such as is known from some spreadsheet programs, e.g. from Microsoft Excel. The table consists of two axes: a row axis and a column axis. These axes consist of characteristic value tuples, which are combinations out of the dimension tables of the star schema. In some OLAP analytical engines, tuple elements are the elements of the key figure structure. However, they are added after the analytical engine in the stack and are only relevant for display purposes. Structures shall not be handled any further in this document.
Between the axes, the data cells are displayed. The data cells are key figure values, and they contain the final OLAP aggregation result, as shown in FIG. 1. The basic problem of a condition data filter is that the key figure value upon which the filter is to be applied is determined via the aggregation process in the analytical engine. But to determine the condition total, the filter needs to be applied before the aggregation. Also normally the data filter (the slicer) is applied at the database level. The database cannot handle the key figure data filters. Accordingly, what is needed is a way to transform a filter defined by an OLAP condition into a characteristic data filter.